from io import BytesIO
from flask import Blueprint
from flask import request,jsonify
import sqlite3
import time
import os
import base64
from PIL import Image

misc_blue = Blueprint('misc', __name__)

def base64_to_img(path,base64_str):
    """
    base64_str = "..." 
    前缀 data:image/png;base64
    内容 iVBO...
    """
    context=base64_str.split(",")[-1]  # 将base64_str以“,”分割为两部分

    img_data = base64.b64decode(context) # 解码时只要内容部分

    image = Image.open(BytesIO(img_data))
    
    # image.resize((260,170)).save(path)
    image.save(path)

def read_table():
    """返回Notice表所有信息"""
    sql = '''SELECT * FROM notice'''
    conn = sqlite3.connect('contest.db')
    c = conn.cursor()
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    return res

def generate_id(_id:int):
    """feedback表，生成id"""
    """参数：id的前6位，日期"""
    """返回：8位id"""
    
    # 查询数据库中已有的数目
    conn = sqlite3.connect('contest.db')
    c = conn.cursor()
    sql = '''SELECT id FROM feedback WHERE id LIKE ? order by id desc''' # 获取当前日期相关的id，并降序排列
    c.execute(sql,(_id+'%',))
    result = c.fetchall()
    print("查询结果：",result)
    conn.close()

    # 取第一个结果的后两位（确保删除操作不会影响到id的生成）
    if len(result)==0:
        num = 1
    else:
        num = int(str(result[0][0])[-2:])+1

    id =  f'{_id}{num:02}'
    print("生成id：",id)
    return id

@misc_blue.route('/updateNotices',methods=['POST'])
def update_notice():
    try:
        # 获取表单数据
        content = request.form['content']
        id = request.form['id']

        # 修改数据
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        sql = '''UPDATE notice SET content=? WHERE id=?'''
        c.execute(sql,(content,id))
        conn.commit()
        conn.close()
        return jsonify({'msg': '修改成功','code':200,'data':{}})
    except Exception as e:
        return jsonify({'msg': '修改失败','code':200,'data':{str(e)}})

@misc_blue.route('/getNotices', methods=['GET'])
def get_notice():
    try:
        res = read_table()
        data = []
        for i in res:
            id, content, ftime = i
            data.append({'id': id, 'content': content, 'time': ftime})

        return jsonify({'msg': '获取成功', 'code': 200, 'data': data})
    except Exception as e:
        print(e)
        return jsonify({'msg': '获取失败', 'code': 200, 'data': str(e)})
   
@misc_blue.route('/addNotices', methods=['POST'])
def add_notice():
    try:
        content = request.form['content']
        ftime = time.strftime('%Y-%m-%d %H:%M:%S') # 格式化时间
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        sql = '''INSERT INTO notice (content,time) VALUES (?,?)'''
        c.execute(sql, (content, ftime))
        conn.commit()
        conn.close()
        return jsonify({'msg': '添加成功', 'code': 200, 'data': {}})
    except Exception as e:
        print(e)
        return jsonify({'msg': '添加失败', 'code': 200, 'data': str(e)})

@misc_blue.route('/deleteNotices', methods=['GET'])
def delete_notice():
    try:
        id = request.args['id']
        sql = '''DELETE FROM notice WHERE id=?'''
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        c.execute(sql, (id,))
        conn.commit()
        conn.close()
        return jsonify({'msg': '删除成功', 'code': 200, 'data': {}})
    except Exception as e:
        print(e)
        return jsonify({'msg': '删除失败', 'code': 200, 'data': str(e)})

@misc_blue.route('/getFeedbacks', methods=['GET'])
def get_feedback():
    try:
        sql = '''SELECT * FROM feedback'''
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        c.execute(sql)
        res = c.fetchall()
        conn.close()
        data = []
        for i in res:
            id, content, ftime,type,pages,status = i
            data.append({'id': id, 'content': content, 'time': ftime,'type':type,'pages':pages,'status':status})

        return jsonify({'msg': '获取成功', 'code': 200, 'data': data})
    except Exception as e:
        print(e)
        return jsonify({'msg': '获取失败', 'code': 200, 'data': str(e)})

@misc_blue.route('/addFeedbacks', methods=['POST'])
def add_feedback():
    try:
        content = request.get_json()['content']
        ftime = time.strftime('%Y-%m-%d %H:%M:%S')
        ntype = request.get_json()['type']
        status = "未处理" 
        id = generate_id(time.strftime('%y%m%d'))

        pages = int(request.get_json()['pages']) # 图片数量
        imgs = request.get_json()['files'] # 图片的base64编码
        ################=上线时修改=######################################################
        folder = '/home/files/jjj/feedback/'+id # 保存图片的文件夹路径
        # folder = 'download/jjj/feedback/'+id # 保存图片的文件夹路径
        os.makedirs(folder, exist_ok=True) # 创建文件夹
        if pages>=1:
            base64_to_img(folder+'/1.jpg',imgs[0])
        if pages>=2:
            base64_to_img(folder+'/2.jpg',imgs[1])
        if pages>=3:
            base64_to_img(folder+'/3.jpg',imgs[2])
        if pages>=4:
            base64_to_img(folder+'/4.jpg',imgs[3])
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        sql = '''INSERT INTO feedback (id,content,time,type,pages,status) VALUES (?,?,?,?,?,?)'''
        c.execute(sql, (id,content, ftime,ntype,pages,status))
        conn.commit()
        conn.close()
        return jsonify({'msg': '添加成功', 'code': 200, 'data': {}})
    except Exception as e:
        print(e)
        return jsonify({'msg': '添加失败', 'code': 400, 'data': str(e)})
    

@misc_blue.route('/execFile', methods=['POST'])
def exec_file():
    try:
        # 获取表单数据
        path = request.form['path'] # 文件路径
        if '..' in path: # 防止路径穿越
            return jsonify({'msg': 'no hack!!!','code':400,'data':'no hack!!!'})
        type = request.form['type'] # 操作类型
        #================上线时修改=======================================================
        root_path = '/home/files/jjj/' # 根目录
        #root_path = 'download/jjj/' # 根目录
        file_path = os.path.join(root_path,path) # 文件路径
        if type == '1': # 上传文件
            file = request.files['file'] # 文件
            file.save(file_path)
        elif type == '3': # 删除文件
            res = os.system(f'rm -r {file_path}')
            if res != 0:
                return jsonify({'msg': '执行失败','code':400,'data':'删除失败'})
        elif type == '2': # 创建文件夹
            print(file_path)
            os.makedirs(file_path, exist_ok=True)
        return jsonify({'msg': '执行成功','code':200,'data':'执行成功'})
    except Exception as e:
        print(str(e))
        return jsonify({'msg': '执行失败','code':400,'data':str(e)})